/* 一键镜像函数 */
drop function if exists one_click_mirror(varchar);
CREATE OR REPLACE FUNCTION one_click_mirror(bak_version_i varchar) 
/* 
  版本号（备份表格式）。
  版本号取值逻辑：
      支持自定义版本，如果入参 bak_version_i 不为空，则为自定义版本。如果为空或者空串 则为 _bak年月日 。例如:_bak20241119
 */
RETURNS TEXT
AS $$
DECLARE
  -- 备份表清单
  v_backup_tables varchar[];
  v_cur record;
  v_source_table_cnt int8;  -- 源表数量
  v_backup_table_cnt int8;  -- 备份表数量
  v_schema varchar;
  v_version_flag varchar;  -- 版本号临时变量
  i int8:=0;
  v_tmp_tablename varchar;
  v_tmp_sequencename varchar;
  v_tmp_privs_info_table_name varchar;
  v_version_is_exists_flag boolean;
BEGIN
  select case when count(*)>1 then True else False end as sl 
    into v_version_is_exists_flag
    from my_tables 
   where table_name ~* bak_version_i;
  if v_version_is_exists_flag then 
    return '版本号 ' || bak_version_i || ' 已存在！';
  else
    -- 版本号取值逻辑。
    if bak_version_i is null or bak_version_i = '' then 
      v_version_flag := '_bak'|| to_char(now(),'yyyymmdd');
    else
      v_version_flag := bak_version_i;
    end if;
    -- RAISE INFO 'v_schema=%',  v_schema;
    -- RAISE INFO 'bak_version_i=%',  bak_version_i;
    
    -- 1、备份整个schema的表数据
    select current_schema() into v_schema;
    select array_agg(table_name) into v_backup_tables
      from db_tables 
     where table_name !~* '_bak[0-9]*$|^BIN'
       and owner !~* 'rdsAdmin'
       and owner = CURRENT_SCHEMA();
    for v_cur in (select v_schema as schemaname, unnest(v_backup_tables) as tablename) loop 
      -- 统计源表数量
      select count(1) 
             into v_source_table_cnt
        from pg_tables t 
       where schemaname = v_schema 
         and tablename = v_cur.tablename;
      -- 统计备份表数量
      select count(1) 
             into v_backup_table_cnt
        from pg_tables t 
       where schemaname = v_schema 
         and tablename = v_cur.tablename || v_version_flag;
      if v_source_table_cnt = 0 then 
        RAISE INFO '【ERROR】当前 schema（ % ）下，“%” 表不存在！', v_schema, v_cur.tablename;
      ELSIF v_source_table_cnt = 1 and v_backup_table_cnt = 0 then 
        i = i + 1;
        RAISE INFO '% - create table %.% as select * from %.%;', i, v_schema, v_cur.tablename || v_version_flag
          , v_schema, v_cur.tablename;
        execute 'create table '||v_schema||'.'||v_cur.tablename|| v_version_flag||' as select * from '||v_schema||'.'||v_cur.tablename||';';
        commit;
        RAISE INFO '    Completed!';
      ELSIF v_source_table_cnt = 1 and v_backup_table_cnt = 1 then 
        RAISE INFO '【It''s been backed up.】当前 schema（%）下，“%” 表已经备份过了！', v_schema, v_cur.tablename || v_version_flag;
      else
        RAISE INFO '【ERROR】未知错误，请联系管理员！';
      end if ;
    end loop; 
    RAISE INFO '【1】备份整个schema的表数据，完成！';
    
    -- 2.备份表结构DDL脚本到表 tab_info_t_bak20251011
    v_tmp_tablename := 'tab_info_t'|| v_version_flag;
    IF NOT EXISTS (select * from pg_tables
                    where schemaname = CURRENT_SCHEMA()
                      and tablename  = v_tmp_tablename
                      and tablename !~* '_bak[0-9]*$|^BIN'
                      ) THEN
      execute 'create table '||CURRENT_SCHEMA()|| '.' ||v_tmp_tablename||' as 
          select pg_get_tabledef(tablename::text)::text as source_tab_def
                ,get_table_ddl(tablename,source_tab_def) as tab_def 
                ,*
            from pg_tables
           where schemaname = CURRENT_SCHEMA()
             and tablename !~* ''_bak[0-9]*$|^BIN''
           ';
      commit;
      RAISE INFO '【2】表结构已备份到 ：% 表！', v_tmp_tablename;
    else 
      RAISE INFO '【2】表结构表 % 已备份过了！', v_tmp_tablename;
    END IF;
    
    -- 3、备份序列信息到 seq_info_t_bak20251011
    v_tmp_sequencename := 'seq_info_t'|| v_version_flag;
    IF NOT EXISTS (select * from pg_tables
                    where schemaname = CURRENT_SCHEMA()
                      and tablename  = v_tmp_sequencename
                      ) THEN
      execute 'create table ' || CURRENT_SCHEMA() || '.' ||v_tmp_sequencename||' as 
  with c as (
  select r.rolname::varchar as owner
        ,n.nspname::varchar as schema
        ,c.relname::varchar as sequence_name
    from pg_class c, pg_roles r, pg_namespace n
   where c.relowner = r.oid
     and c.relnamespace = n.oid
     and c.relkind = ''S''
   )
  ,s as (
  select sequence_owner::varchar as owner
        ,sequence_name::varchar
        ,min_value::varchar
        ,max_value::varchar
        ,increment_by::varchar
        ,cache_size::varchar
        ,cycle_flag::varchar
        ,last_number::varchar
    from my_sequences
   where owner = CURRENT_SCHEMA
  )
  select ''drop sequence if exists ''|| c.sequence_name::varchar || '';'' as drop_seq
        ,''create sequence ''|| c.sequence_name::varchar || '' start with '' || s.last_number::varchar || '';''  as create_seq
        ,c.owner::varchar,c.schema::varchar,c.sequence_name::varchar
        ,s.min_value::varchar
        ,s.max_value::varchar
        ,s.increment_by::varchar
        ,s.cache_size::varchar
        ,s.cycle_flag::varchar
        ,s.last_number::varchar
    from C
   inner join s on c.owner = s.owner and c.sequence_name = s.sequence_name
   order by schema, sequence_name'
           ;
      commit;
      RAISE INFO '【3】序列结构已备份到 ：% 表！', v_tmp_sequencename;
    else 
      RAISE INFO '【3】序列结构表 % 已备份过了！', v_tmp_sequencename;
    END IF;

    -- 4、备份 授权情况
    v_tmp_privs_info_table_name := 'privs_info_t'|| v_version_flag;
    IF NOT EXISTS (select * from pg_tables
                    where schemaname = CURRENT_SCHEMA()
                      and tablename  = v_tmp_privs_info_table_name) THEN
      execute 'create table ' || CURRENT_SCHEMA() || '.' ||v_tmp_privs_info_table_name||' as 
      select * from privs_info_v'
      ;
      commit;
      RAISE INFO '【4】授权情况已备份到 ：% 表！', v_tmp_privs_info_table_name;
    else 
      RAISE INFO '【4】授权情况表 % 已备份过了！', v_tmp_privs_info_table_name;
    END IF;
    return 'OK';
  end if;
END;
$$ LANGUAGE plpgsql;



